{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "8239e8eb-724c-44df-b651-c2dd02f1d96c",
   "metadata": {},
   "source": [
    "<!---\n",
    "  Licensed to the Apache Software Foundation (ASF) under one\n",
    "  or more contributor license agreements.  See the NOTICE file\n",
    "  distributed with this work for additional information\n",
    "  regarding copyright ownership.  The ASF licenses this file\n",
    "  to you under the Apache License, Version 2.0 (the\n",
    "  \"License\"); you may not use this file except in compliance\n",
    "  with the License.  You may obtain a copy of the License at\n",
    "\n",
    "    http://www.apache.org/licenses/LICENSE-2.0\n",
    "\n",
    "  Unless required by applicable law or agreed to in writing,\n",
    "  software distributed under the License is distributed on an\n",
    "  \"AS IS\" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY\n",
    "  KIND, either express or implied.  See the License for the\n",
    "  specific language governing permissions and limitations\n",
    "  under the License.\n",
    "-->\n",
    "\n",
    "# SedonaDB Overture Examples\n",
    "\n",
    "> Note: Before running this notebook, ensure that you have installed SedonaDB: `pip install \"apache-sedona[db]\"`\n",
    "\n",
    "This notebook demonstrates how to query and analyze the [Overture Maps](https://overturemaps.org/) dataset using SedonaDB.\n",
    "\n",
    "The notebook explains how to:\n",
    "\n",
    "* Load Overture data for the `buildings` and `divisions` themes directly from S3.\n",
    "* Perform spatial queries to find features within a specific geographic area.\n",
    "* Optimize subsequent query performance by caching a subset of data in memory."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c5e580ff",
   "metadata": {},
   "outputs": [],
   "source": [
    "%pip install lonboard"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "6d6fa0ab-b4ed-4e60-b099-a1af0998b486",
   "metadata": {},
   "outputs": [],
   "source": [
    "import sedona.db\n",
    "import os\n",
    "\n",
    "os.environ[\"AWS_SKIP_SIGNATURE\"] = \"true\"\n",
    "os.environ[\"AWS_DEFAULT_REGION\"] = \"us-west-2\"\n",
    "\n",
    "sd = sedona.db.connect()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4f44adfb-2973-4a65-b4f2-d24b28700b79",
   "metadata": {},
   "source": [
    "## Overture buildings table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "52855769-4872-472a-9c42-afced3d85ca8",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = sd.read_parquet(\n",
    "    \"s3://overturemaps-us-west-2/release/2025-08-20.0/theme=buildings/type=building/\"\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "b45b5e5c-64ed-49ba-a8aa-9f2292f617c6",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "┌──────────────────────────────────────┬─────────────────────────────────────────┬───┬─────────────┐\n",
      "│                  id                  ┆                 geometry                ┆ … ┆ roof_height │\n",
      "│                 utf8                 ┆                 geometry                ┆   ┆   float64   │\n",
      "╞══════════════════════════════════════╪═════════════════════════════════════════╪═══╪═════════════╡\n",
      "│ afc55d29-5916-42ad-8f9c-76ba9d749be9 ┆ POLYGON((-74.5920939 4.893956,-74.5920… ┆ … ┆             │\n",
      "├╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌┤\n",
      "│ 9c7ece54-88ff-48d4-8b48-11959fb058a1 ┆ POLYGON((-74.5906253 4.8896057,-74.590… ┆ … ┆             │\n",
      "├╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌┤\n",
      "│ 24fde8c7-6771-4205-b9da-2599d825f85f ┆ POLYGON((-74.5905666 4.8896679,-74.590… ┆ … ┆             │\n",
      "├╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌┤\n",
      "│ 79bdbb6f-5a9f-4b35-8e30-eb9019a6a1d7 ┆ POLYGON((-74.5905272 4.8896867,-74.590… ┆ … ┆             │\n",
      "├╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌┤\n",
      "│ 45ca1264-1062-41de-9375-82d93d93ee31 ┆ POLYGON((-74.590441 4.8897205,-74.5904… ┆ … ┆             │\n",
      "├╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌┤\n",
      "│ b22e0827-bc09-439f-85cf-5401db561a23 ┆ POLYGON((-74.5903646 4.889949,-74.5903… ┆ … ┆             │\n",
      "├╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌┤\n",
      "│ 754bf2dd-81a3-498f-910d-b2b2cc18e1a2 ┆ POLYGON((-74.589949 4.8891923,-74.5899… ┆ … ┆             │\n",
      "├╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌┤\n",
      "│ 77464ae4-77d9-45ac-ac28-e6f972de9363 ┆ POLYGON((-74.5898331 4.889214,-74.5897… ┆ … ┆             │\n",
      "├╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌┤\n",
      "│ 0f02d3f6-bbec-4cc0-89e5-65c3c4676cf1 ┆ POLYGON((-74.5899092 4.8892714,-74.589… ┆ … ┆             │\n",
      "├╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌┤\n",
      "│ 696976a2-ff13-4b89-9953-8fd74b400828 ┆ POLYGON((-74.5902424 4.889916,-74.5902… ┆ … ┆             │\n",
      "└──────────────────────────────────────┴─────────────────────────────────────────┴───┴─────────────┘\n"
     ]
    }
   ],
   "source": [
    "df.limit(10).show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "e37a023e-0e80-403a-a65b-b5a190004f72",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_view(\"buildings\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "ebfe4776-e08f-4f38-97fc-fca8ec6fc364",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "┌────────────┐\n",
      "│  count(*)  │\n",
      "│    int64   │\n",
      "╞════════════╡\n",
      "│ 2539170484 │\n",
      "└────────────┘\n"
     ]
    }
   ],
   "source": [
    "# the buildings table is large and contains billions of rows\n",
    "sd.sql(\"\"\"\n",
    "SELECT\n",
    "    COUNT(*)\n",
    "FROM\n",
    "    buildings\n",
    "\"\"\").show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "b73f670d-0d10-4a7a-bfc7-e2abe5d9edd2",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "SedonaSchema with 24 fields:\n",
       "  id: utf8<Utf8View>\n",
       "  geometry: geometry<WkbView(ogc:crs84)>\n",
       "  bbox: struct<Struct(xmin Float32, xmax Float32, ymin Float32, ymax Float32)>\n",
       "  version: int32<Int32>\n",
       "  sources: list<List(Field { name: \"element\", data_type: Struct([Field { name: \"property\", data_type: Utf8, nullable: true, dict_id: 0, dict_is_ordered: false, metadata: {} }, Field { name: \"dataset\", data_type: Utf8, nullable: true, dict_id: 0, dict_is_ordered: false, metadata: {} }, Field { name: \"record_id\", data_type: Utf8, nullable: true, dict_id: 0, dict_is_ordered: false, metadata: {} }, Field { name: \"update_time\", data_type: Utf8, nullable: true, dict_id: 0, dict_is_ordered: false, metadata: {} }, Field { name: \"confidence\", data_type: Float64, nullable: true, dict_id: 0, dict_is_ordered: false, metadata: {} }, Field { name: \"between\", data_type: List(Field { name: \"element\", data_type: Float64, nullable: true, dict_id: 0, dict_is_ordered: false, metadata: {} }), nullable: true, dict_id: 0, dict_is_ordered: false, metadata: {} }]), nullable: true, dict_id: 0, dict_is_ordered: false, metadata: {} })>\n",
       "  level: int32<Int32>\n",
       "  subtype: utf8<Utf8View>\n",
       "  class: utf8<Utf8View>\n",
       "  height: float64<Float64>\n",
       "  names: struct<Struct(primary Utf8, common Map(Field { name: \"key_value\", data_type: Struct([Field { name: \"key\", data_type: Utf8, nullable: false, dict_id: 0, dict_is_ordered: false, metadata: {} }, Field { name: \"value\", data_type: Utf8, nullable: true, dict_id: 0, dict_is_ordered: false, metadata: {} }]), nullable: false, dict_id: 0, dict_is_ordered: false, metadata: {} }, false), rules List(Field { name: \"element\", data_type: Struct([Field { name: \"variant\", data_type: Utf8, nullable: true, dict_id: 0, dict_is_ordered: false, metadata: {} }, Field { name: \"language\", data_type: Utf8, nullable: true, dict_id: 0, dict_is_ordered: false, metadata: {} }, Field { name: \"perspectives\", data_type: Struct([Field { name: \"mode\", data_type: Utf8, nullable: true, dict_id: 0, dict_is_ordered: false, metadata: {} }, Field { name: \"countries\", data_type: List(Field { name: \"element\", data_type: Utf8, nullable: true, dict_id: 0, dict_is_ordered: false, metadata: {} }), nullable: true, dict_id: 0, dict_is_ordered: false, metadata: {} }]), nullable: true, dict_id: 0, dict_is_ordered: false, metadata: {} }, Field { name: \"value\", data_type: Utf8, nullable: true, dict_id: 0, dict_is_ordered: false, metadata: {} }, Field { name: \"between\", data_type: List(Field { name: \"element\", data_type: Float64, nullable: true, dict_id: 0, dict_is_ordered: false, metadata: {} }), nullable: true, dict_id: 0, dict_is_ordered: false, metadata: {} }, Field { name: \"side\", data_type: Utf8, nullable: true, dict_id: 0, dict_is_ordered: false, metadata: {} }]), nullable: true, dict_id: 0, dict_is_ordered: false, metadata: {} }))>\n",
       "  has_parts: boolean<Boolean>\n",
       "  is_underground: boolean<Boolean>\n",
       "  num_floors: int32<Int32>\n",
       "  num_floors_underground: int32<Int32>\n",
       "  min_height: float64<Float64>\n",
       "  min_floor: int32<Int32>\n",
       "  facade_color: utf8<Utf8View>\n",
       "  facade_material: utf8<Utf8View>\n",
       "  roof_material: utf8<Utf8View>\n",
       "  roof_shape: utf8<Utf8View>\n",
       "  roof_direction: float64<Float64>\n",
       "  roof_orientation: utf8<Utf8View>\n",
       "  roof_color: utf8<Utf8View>\n",
       "  roof_height: float64<Float64>"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# check out the schema of the buildings table to see what it contains\n",
    "df.schema"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "68d1b68c-dd26-45c2-944f-61138b212943",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "┌─────────────────────────┬────────────────────┬────────────┬────────────┬─────────────────────────┐\n",
      "│            id           ┆       height       ┆ num_floors ┆ roof_shape ┆         centroid        │\n",
      "│           utf8          ┆       float64      ┆    int32   ┆    utf8    ┆         geometry        │\n",
      "╞═════════════════════════╪════════════════════╪════════════╪════════════╪═════════════════════════╡\n",
      "│ 1b9040c2-2e79-4f56-aba… ┆               22.4 ┆            ┆            ┆ POINT(-74.230407502993… │\n",
      "├╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┤\n",
      "│ 1b5e1cd2-d697-489e-892… ┆               21.5 ┆            ┆            ┆ POINT(-74.231451103592… │\n",
      "├╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┤\n",
      "│ c1afdf78-bf84-4b8f-ae1… ┆               20.9 ┆            ┆            ┆ POINT(-74.232593032240… │\n",
      "├╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┤\n",
      "│ 88f36399-b09f-491b-bb6… ┆               24.5 ┆            ┆            ┆ POINT(-74.231878209597… │\n",
      "├╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┤\n",
      "│ df37a283-f5bd-4822-a05… ┆ 24.154542922973633 ┆            ┆            ┆ POINT(-74.241910239840… │\n",
      "└─────────────────────────┴────────────────────┴────────────┴────────────┴─────────────────────────┘\n"
     ]
    }
   ],
   "source": [
    "# find all the buildings in New York City that are taller than 20 meters\n",
    "nyc_bbox_wkt = (\n",
    "    \"POLYGON((-74.2591 40.4774, -74.2591 40.9176, -73.7004 40.9176, \"\n",
    "    \"-73.7004 40.4774, -74.2591 40.4774))\"\n",
    ")\n",
    "sd.sql(f\"\"\"\n",
    "SELECT\n",
    "    id,\n",
    "    height,\n",
    "    num_floors,\n",
    "    roof_shape,\n",
    "    ST_Centroid(geometry) as centroid\n",
    "FROM\n",
    "    buildings\n",
    "WHERE\n",
    "    is_underground = FALSE\n",
    "    AND height IS NOT NULL\n",
    "    AND height > 20\n",
    "    AND ST_Intersects(\n",
    "        geometry,\n",
    "        ST_SetSRID(ST_GeomFromText('{nyc_bbox_wkt}'), 4326)\n",
    "    )\n",
    "LIMIT 5;\n",
    "\"\"\").show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e07fcdc1-962b-4dce-90cb-bf715432e299",
   "metadata": {},
   "source": [
    "## Overture divisions table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "d9f122d3-4d90-46b0-ab9a-259a71cc423b",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = sd.read_parquet(\n",
    "    \"s3://overturemaps-us-west-2/release/2025-08-20.0/theme=divisions/type=division_area/\"\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "19a75b19-0b56-4167-b3f1-73a171ecc480",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "┌─────────────────┬────────────────┬────────────────┬───┬────────────────┬────────┬────────────────┐\n",
      "│        id       ┆    geometry    ┆      bbox      ┆ … ┆ is_territorial ┆ region ┆   division_id  │\n",
      "│       utf8      ┆    geometry    ┆     struct     ┆   ┆     boolean    ┆  utf8  ┆      utf8      │\n",
      "╞═════════════════╪════════════════╪════════════════╪═══╪════════════════╪════════╪════════════════╡\n",
      "│ ae415364-9d06-… ┆ MULTIPOLYGON(… ┆ {xmin: 29.525… ┆ … ┆ true           ┆ BY-MA  ┆ 7665e731-6552… │\n",
      "├╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┤\n",
      "│ d712e9e1-84c6-… ┆ POLYGON((29.5… ┆ {xmin: 29.577… ┆ … ┆ true           ┆ BY-MA  ┆ 4605a8a3-c576… │\n",
      "├╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┤\n",
      "│ bee9663a-af50-… ┆ POLYGON((29.5… ┆ {xmin: 29.587… ┆ … ┆ true           ┆ BY-MA  ┆ 826a2333-7584… │\n",
      "├╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┤\n",
      "│ a083b661-eb7b-… ┆ POLYGON((29.4… ┆ {xmin: 29.326… ┆ … ┆ true           ┆ BY-MA  ┆ 17456e57-ebb6… │\n",
      "├╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┤\n",
      "│ 83ef05fa-43ad-… ┆ POLYGON((29.6… ┆ {xmin: 29.598… ┆ … ┆ true           ┆ BY-MA  ┆ a44666eb-c61d… │\n",
      "├╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┤\n",
      "│ 4d01e5b4-86f9-… ┆ POLYGON((29.5… ┆ {xmin: 29.531… ┆ … ┆ true           ┆ BY-MA  ┆ 77cdd054-ec26… │\n",
      "├╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┤\n",
      "│ c2a12db9-a1d0-… ┆ POLYGON((29.5… ┆ {xmin: 29.537… ┆ … ┆ true           ┆ BY-MA  ┆ 4faa3881-e9a8… │\n",
      "├╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┤\n",
      "│ 7c290d7b-3f7f-… ┆ POLYGON((29.6… ┆ {xmin: 29.607… ┆ … ┆ true           ┆ BY-MA  ┆ e5233235-d513… │\n",
      "├╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┤\n",
      "│ 579063f0-f751-… ┆ POLYGON((29.6… ┆ {xmin: 29.614… ┆ … ┆ true           ┆ BY-MA  ┆ 3f0c8f4e-c9c8… │\n",
      "├╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┤\n",
      "│ 675aab04-ed91-… ┆ POLYGON((29.6… ┆ {xmin: 29.616… ┆ … ┆ true           ┆ BY-MA  ┆ 02f5979e-7976… │\n",
      "└─────────────────┴────────────────┴────────────────┴───┴────────────────┴────────┴────────────────┘\n"
     ]
    }
   ],
   "source": [
    "# inspect a few rows of the data\n",
    "df.show(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "03b951de-3397-4fcf-9baf-50e139a38dd4",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_view(\"division_area\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "9c6bd69d-9407-432a-bdc8-d60976237a3a",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "┌──────────┐\n",
      "│ count(*) │\n",
      "│   int64  │\n",
      "╞══════════╡\n",
      "│  1035749 │\n",
      "└──────────┘\n"
     ]
    }
   ],
   "source": [
    "sd.sql(\"\"\"\n",
    "SELECT\n",
    "    COUNT(*)\n",
    "FROM division_area\n",
    "\"\"\").show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "75a6d0ed-9767-4d36-a77a-4afb7952fbe4",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "SedonaSchema with 13 fields:\n",
       "  id: utf8<Utf8View>\n",
       "  geometry: geometry<WkbView(ogc:crs84)>\n",
       "  bbox: struct<Struct(xmin Float32, xmax Float32, ymin Float32, ymax Float32)>\n",
       "  country: utf8<Utf8View>\n",
       "  version: int32<Int32>\n",
       "  sources: list<List(Field { name: \"element\", data_type: Struct([Field { name: \"property\", data_type: Utf8, nullable: true, dict_id: 0, dict_is_ordered: false, metadata: {} }, Field { name: \"dataset\", data_type: Utf8, nullable: true, dict_id: 0, dict_is_ordered: false, metadata: {} }, Field { name: \"record_id\", data_type: Utf8, nullable: true, dict_id: 0, dict_is_ordered: false, metadata: {} }, Field { name: \"update_time\", data_type: Utf8, nullable: true, dict_id: 0, dict_is_ordered: false, metadata: {} }, Field { name: \"confidence\", data_type: Float64, nullable: true, dict_id: 0, dict_is_ordered: false, metadata: {} }, Field { name: \"between\", data_type: List(Field { name: \"element\", data_type: Float64, nullable: true, dict_id: 0, dict_is_ordered: false, metadata: {} }), nullable: true, dict_id: 0, dict_is_ordered: false, metadata: {} }]), nullable: true, dict_id: 0, dict_is_ordered: false, metadata: {} })>\n",
       "  subtype: utf8<Utf8View>\n",
       "  class: utf8<Utf8View>\n",
       "  names: struct<Struct(primary Utf8, common Map(Field { name: \"key_value\", data_type: Struct([Field { name: \"key\", data_type: Utf8, nullable: false, dict_id: 0, dict_is_ordered: false, metadata: {} }, Field { name: \"value\", data_type: Utf8, nullable: true, dict_id: 0, dict_is_ordered: false, metadata: {} }]), nullable: false, dict_id: 0, dict_is_ordered: false, metadata: {} }, false), rules List(Field { name: \"element\", data_type: Struct([Field { name: \"variant\", data_type: Utf8, nullable: true, dict_id: 0, dict_is_ordered: false, metadata: {} }, Field { name: \"language\", data_type: Utf8, nullable: true, dict_id: 0, dict_is_ordered: false, metadata: {} }, Field { name: \"perspectives\", data_type: Struct([Field { name: \"mode\", data_type: Utf8, nullable: true, dict_id: 0, dict_is_ordered: false, metadata: {} }, Field { name: \"countries\", data_type: List(Field { name: \"element\", data_type: Utf8, nullable: true, dict_id: 0, dict_is_ordered: false, metadata: {} }), nullable: true, dict_id: 0, dict_is_ordered: false, metadata: {} }]), nullable: true, dict_id: 0, dict_is_ordered: false, metadata: {} }, Field { name: \"value\", data_type: Utf8, nullable: true, dict_id: 0, dict_is_ordered: false, metadata: {} }, Field { name: \"between\", data_type: List(Field { name: \"element\", data_type: Float64, nullable: true, dict_id: 0, dict_is_ordered: false, metadata: {} }), nullable: true, dict_id: 0, dict_is_ordered: false, metadata: {} }, Field { name: \"side\", data_type: Utf8, nullable: true, dict_id: 0, dict_is_ordered: false, metadata: {} }]), nullable: true, dict_id: 0, dict_is_ordered: false, metadata: {} }))>\n",
       "  is_land: boolean<Boolean>\n",
       "  is_territorial: boolean<Boolean>\n",
       "  region: utf8<Utf8View>\n",
       "  division_id: utf8<Utf8View>"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.schema"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "f1f7158c-ef2b-4377-9bee-180309ddd553",
   "metadata": {},
   "outputs": [],
   "source": [
    "# get all the divisions in Nova Scotia and save them in memory with to_memtable()\n",
    "nova_scotia_bbox_wkt = (\n",
    "    \"POLYGON((-66.5 43.4, -66.5 47.1, -59.8 47.1, -59.8 43.4, -66.5 43.4))\"\n",
    ")\n",
    "ns = sd.sql(f\"\"\"\n",
    "SELECT\n",
    "    country, region, names, geometry\n",
    "FROM division_area\n",
    "WHERE\n",
    "    ST_Intersects(\n",
    "        geometry,\n",
    "        ST_SetSRID(ST_GeomFromText('{nova_scotia_bbox_wkt}'), 4326)\n",
    "    )\n",
    "\"\"\").to_memtable()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "27e6909d-06fa-438b-88e0-d300fd2fb1ec",
   "metadata": {},
   "outputs": [],
   "source": [
    "ns.to_view(\"ns_divisions\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "2dec92d8-a374-4021-990a-e50f5769516e",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = sd.sql(\"\"\"\n",
    "SELECT UNNEST(names), geometry\n",
    "FROM ns_divisions\n",
    "WHERE region = 'CA-NS'\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "7f39a283-0eee-4f72-a30a-8dd9fa1aaa69",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "┌────────────────────────┬────────────────────────┬────────────────────────┬───────────────────────┐\n",
      "│ __unnest_placeholder(n ┆ __unnest_placeholder(n ┆ __unnest_placeholder(n ┆        geometry       │\n",
      "│ s_divisions.names).pr… ┆ s_divisions.names).co… ┆ s_divisions.names).ru… ┆        geometry       │\n",
      "╞════════════════════════╪════════════════════════╪════════════════════════╪═══════════════════════╡\n",
      "│ Seal Island            ┆                        ┆                        ┆ POLYGON((-66.0528452… │\n",
      "├╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┤\n",
      "│ Mud Island             ┆                        ┆                        ┆ POLYGON((-66.0222822… │\n",
      "└────────────────────────┴────────────────────────┴────────────────────────┴───────────────────────┘\n",
      "CPU times: user 1.51 ms, sys: 1.69 ms, total: 3.2 ms\n",
      "Wall time: 3.14 ms\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "# this executes quickly because the Nova Scotia data was persisted in memory with `to_memtable()`\n",
    "df.show(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fc1d2023-c83a-4010-808b-212161b1b577",
   "metadata": {},
   "source": [
    "## Visualize the results with lonboard"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "f78583fd-a73a-4169-9c45-74d8026bb5fb",
   "metadata": {
    "collapsed": true,
    "jupyter": {
     "outputs_hidden": true
    }
   },
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "560eccc4e795458b815ef78a70548d82",
       "version_major": 2,
       "version_minor": 1
      },
      "text/plain": [
       "Map(basemap_style=<CartoBasemap.DarkMatter: 'https://basemaps.cartocdn.com/gl/dark-matter-gl-style/style.json'…"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import lonboard\n",
    "\n",
    "lonboard.viz(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0f668f51-462e-4e18-b774-c047140b224a",
   "metadata": {},
   "source": [
    "![Lonboard NS](image/lonboard_ns.png)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.12.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
